﻿USE QLBH
GO
-------------------------------------NHA CUNG CAP-------------------------
CREATE PROC INSERT_UPDATE_DELETE_NHACC
@MANHACC CHAR(4),
@TENNHACC NVARCHAR(60),
@DIACHI NVARCHAR(100),
@DIENTHOAI NVARCHAR(20),
@TYPE INT
AS
BEGIN 
	IF (@TYPE=0) --@TYPE=0: INSERT NEW ROW
	BEGIN
		INSERT INTO NHACC VALUES(@MANHACC,@TENNHACC,@DIACHI,@DIENTHOAI)
	END
	ELSE IF( @TYPE=1)  --@TYPE=1: UPDATE INFO
	BEGIN
		UPDATE NHACC SET TENNHACC=@TENNHACC, DIACHI=@DIACHI,DIENTHOAI=@DIENTHOAI WHERE MANHACC=@MANHACC
	END
	ELSE  --@TYPE=2: DELETE ROW
	BEGIN
		DELETE FROM NHACC WHERE MANHACC=@MANHACC
	END
END
GO
-------------------------------------VAT TU-------------------------
CREATE PROC INSERT_UPDATE_DELETE_VATTU
@MAVTU CHAR(4),
@TENVTU NVARCHAR(100),
@DVTINH NVARCHAR(10),
@PHANTRAM REAL,
@HINH NVARCHAR(200),
@TYPE INT
AS
BEGIN 
	IF (@TYPE=0) --@TYPE=0: INSERT NEW ROW
	BEGIN
		INSERT INTO VATTU VALUES(@MAVTU,@TENVTU,@DVTINH,@PHANTRAM,@HINH)
	END
	ELSE IF( @TYPE=1)  --@TYPE=1: UPDATE INFO
	BEGIN
		UPDATE VATTU SET TENVTU=@TENVTU, DVTINH=@DVTINH,PHANTRAM=@PHANTRAM,HINH=@HINH WHERE MAVTU=@MAVTU
	END
	ELSE  --@TYPE=2: DELETE ROW
	BEGIN
		DELETE FROM VATTU WHERE MAVTU=@MAVTU
	END
END
GO
-------------------------------------PHIEU NHAP-------------------------
CREATE PROC INSERT_UPDATE_DELETE_PNHAP
@SOPN CHAR(4),
@NGAYNHAP DATETIME,
@SODH CHAR(4),
@TYPE INT
AS
BEGIN 
	IF (@TYPE=0) --@TYPE=0: INSERT NEW ROW
	BEGIN
		INSERT INTO PNHAP VALUES(@SOPN,@NGAYNHAP,@SODH)
	END
	ELSE IF( @TYPE=1)  --@TYPE=1: UPDATE INFO
	BEGIN
		UPDATE PNHAP SET NGAYNHAP=@NGAYNHAP, SODH=@SODH WHERE SOPN=@SOPN
	END
	ELSE  --@TYPE=2: DELETE ROW
	BEGIN
		DELETE FROM CTPNHAP WHERE SOPN=@SOPN
		DELETE FROM PNHAP WHERE SOPN=@SOPN
	END
END
GO

CREATE PROC LOAD_CTPNHAP_BY_SOPN
@SOPN CHAR(4)
AS
BEGIN
	SELECT SOPN,CTPNHAP.MAVTU,TENVTU,SLNHAP,DGNHAP
	FROM CTPNHAP, VATTU
	WHERE CTPNHAP.MAVTU=VATTU.MAVTU AND CTPNHAP.SOPN=@SOPN
END
GO

CREATE PROC INSERT_UPDATE_DELETE_CTPNHAP
@SOPN CHAR(4),
@MAVTU CHAR(4),
@SLNHAP INT,
@DGNHAP MONEY,
@TYPE INT
AS
BEGIN
	IF (@TYPE=0) --@TYPE=0: INSERT NEW ROW
	BEGIN
		INSERT INTO CTPNHAP VALUES(@SOPN,@MAVTU,@SLNHAP,@DGNHAP)
	END
	ELSE IF( @TYPE=1)  --@TYPE=1: UPDATE INFO
	BEGIN
		UPDATE CTPNHAP SET SLNHAP=@SLNHAP, DGNHAP=@DGNHAP WHERE SOPN=@SOPN AND MAVTU=@MAVTU
	END
	ELSE  --@TYPE=2: DELETE ROW
	BEGIN
		DELETE FROM CTPNHAP WHERE SOPN=@SOPN AND MAVTU=@MAVTU
	END
END

-------------------------------------PHIEU XUAT-------------------------
GO
CREATE PROC INSERT_UPDATE_DELETE_PXUAT
@SOPX CHAR(4),
@NGAYXUAT DATETIME,
@TENKH NVARCHAR(100),
@TYPE INT
AS
BEGIN 
	IF (@TYPE=0) --@TYPE=0: INSERT NEW ROW
	BEGIN
		INSERT INTO PXUAT VALUES(@SOPX,@NGAYXUAT,@TENKH)
	END
	ELSE IF( @TYPE=1)  --@TYPE=1: UPDATE INFO
	BEGIN
		UPDATE PXUAT SET NGAYXUAT=@NGAYXUAT, TENKH=@TENKH WHERE SOPX=@SOPX
	END
	ELSE  --@TYPE=2: DELETE ROW
	BEGIN
		DELETE FROM CTPXUAT WHERE SOPX=@SOPX
		DELETE FROM PXUAT WHERE SOPX=@SOPX
	END
END
GO


CREATE PROC LOAD_CTPXUAT_BY_SOPX
@SOPX CHAR(4)
AS
BEGIN
	SELECT SOPX,CTPXUAT.MAVTU,TENVTU,SLXUAT,DGXUAT
	FROM CTPXUAT, VATTU
	WHERE CTPXUAT.MAVTU=VATTU.MAVTU AND CTPXUAT.SOPX=@SOPX
END
GO

CREATE PROC INSERT_UPDATE_DELETE_CTPXUAT
@SOPX CHAR(4),
@MAVTU CHAR(4),
@SLXUAT INT,
@DGXUAT MONEY,
@TYPE INT
AS
BEGIN
	IF (@TYPE=0) --@TYPE=0: INSERT NEW ROW
	BEGIN
		INSERT INTO CTPXUAT VALUES(@SOPX,@MAVTU,@SLXUAT,@DGXUAT)
	END
	ELSE IF( @TYPE=1)  --@TYPE=1: UPDATE INFO
	BEGIN
		UPDATE CTPXUAT SET SLXUAT=@SLXUAT, DGXUAT=@DGXUAT WHERE SOPX=@SOPX AND MAVTU=@MAVTU
	END
	ELSE  --@TYPE=2: DELETE ROW
	BEGIN
		DELETE FROM CTPXUAT WHERE SOPX=@SOPX AND MAVTU=@MAVTU
	END
END
GO
-------------------------------------DONDH-----------------------
CREATE PROC INSERT_UPDATE_DELETE_DONDH
@SODH CHAR(4),
@NGAYDH DATETIME,
@MANHACC CHAR(4),
@TYPE INT
AS
BEGIN 
	IF (@TYPE=0) --@TYPE=0: INSERT NEW ROW
	BEGIN
		INSERT INTO DONDH VALUES(@SODH,@NGAYDH,@MANHACC)
	END
	ELSE IF( @TYPE=1)  --@TYPE=1: UPDATE INFO
	BEGIN
		UPDATE DONDH SET NGAYDH=@NGAYDH, MANHACC=@MANHACC WHERE SODH=@SODH
	END
	ELSE  --@TYPE=2: DELETE ROW
	BEGIN
		DELETE FROM CTDONDH WHERE SODH=@SODH
		DELETE FROM DONDH WHERE SODH=@SODH
	END
END
GO

CREATE PROC LOAD_CTDONDH_BY_SODH
@SODH CHAR(4)
AS
BEGIN
	SELECT SODH,CTDONDH.MAVTU,TENVTU,SLDAT
	FROM CTDONDH, VATTU
	WHERE CTDONDH.MAVTU=VATTU.MAVTU AND CTDONDH.SODH=@SODH
END
GO

CREATE PROC INSERT_UPDATE_DELETE_CTDONDH
@SODH CHAR(4),
@MAVTU CHAR(4),
@SLDAT INT,
@TYPE INT
AS
BEGIN
	IF (@TYPE=0) --@TYPE=0: INSERT NEW ROW
	BEGIN
		INSERT INTO CTDONDH VALUES(@SODH,@MAVTU,@SLDAT)
	END
	ELSE IF( @TYPE=1)  --@TYPE=1: UPDATE INFO
	BEGIN
		UPDATE CTDONDH SET SLDAT=@SLDAT WHERE SODH=@SODH AND MAVTU=@MAVTU
	END
	ELSE  --@TYPE=2: DELETE ROW
	BEGIN
		DELETE FROM CTDONDH WHERE SODH=@SODH AND MAVTU=@MAVTU
	END
END

---------------------------------------TON KHO-------------------------------
ALTER PROC LOAD_TONKHO
@FROMDATE DATETIME,
@TODATE DATETIME,
@MAVTU CHAR(4)
AS
	IF (@MAVTU IS NULL) OR (@MAVTU='')
	BEGIN
	SELECT NAMTHANG,TONKHO.MAVTU,VATTU.TENVTU,SLDAU,TONGSLNHAP,TONGSLXUAT,SLCUOI
	FROM TONKHO, VATTU
	WHERE CONVERT(DATETIME,RIGHT(NAMTHANG,2)+'/1/'+LEFT(NAMTHANG,4))>= @FROMDATE
	     AND CONVERT(DATETIME,RIGHT(NAMTHANG,2)+'/1/'+LEFT(NAMTHANG,4)) <=@TODATE
		 AND TONKHO.MAVTU=VATTU.MAVTU
	END
	ELSE
	BEGIN
		SELECT NAMTHANG,TONKHO.MAVTU,VATTU.TENVTU,SLDAU,TONGSLNHAP,TONGSLXUAT,SLCUOI
		FROM TONKHO, VATTU
		WHERE CONVERT(DATETIME,RIGHT(NAMTHANG,2)+'/1/'+LEFT(NAMTHANG,4))>= @FROMDATE
	     AND CONVERT(DATETIME,RIGHT(NAMTHANG,2)+'/1/'+LEFT(NAMTHANG,4)) <=@TODATE
			 AND TONKHO.MAVTU=VATTU.MAVTU AND TONKHO.MAVTU=@MAVTU
	END
GO

--a.Fn_TongNhapThang(@NamThang, @Mavtu) trả về tổng số lượng đã nhập trong tháng của một vật tư.
-- Chú ý @NamThang có dạng yyyyMM và không dùng bảng TONKHO
ALTER FUNCTION DBO.fn_TONGNHAPTHANG (@NAMTHANG VARCHAR(6),@MA_VT CHAR(4))
RETURNS INT
AS
BEGIN
	DECLARE @TONG_NHAP INT
	SELECT @TONG_NHAP=ISNULL(SUM(SLNHAP),0)
	FROM PNHAP A,CTPNHAP B
	WHERE A.SOPN=B.SOPN AND YEAR(NGAYNHAP)=LEFT(@NAMTHANG,4) AND MONTH(NGAYNHAP)=RIGHT(@NAMTHANG,2) AND MAVTU=@MA_VT
	GROUP BY MAVTU
	RETURN @TONG_NHAP
END
GO

--b.Fn_TongXuatThang(@NamThang, @Mavtu) trả về tổng số lượng đã xuất trong tháng của một vật tư.
--Chú ý @NamThang có dạng yyyyMM và không dùng bảng TONKHO.
ALTER FUNCTION DBO.fn_TONGXUATTHANG (@NAMTHANG VARCHAR(6),@MA_VT CHAR(4))
RETURNS INT
AS
BEGIN
	DECLARE @TONG_XUAT INT
	SELECT @TONG_XUAT=ISNULL(SUM(SLXUAT),0)
	FROM PXUAT A,CTPXUAT B
	WHERE A.SOPX=B.SOPX AND YEAR(NGAYXUAT)=LEFT(@NAMTHANG,4) AND MONTH(NGAYXUAT)=RIGHT(@NAMTHANG,2) AND MAVTU=@MA_VT
	GROUP BY MAVTU
	RETURN @TONG_XUAT
END
GO
--c.Sử dụng hàm Fn_TongNhapThang và hàm Fn_TongXuatThang đã tạo để viết thủ tục nội tại 
--spud_TONKHO_CapNhatTongSL_NX gồm một tham số là Năm tháng, 
--dùng để cập nhật lại các cột TongSLNhap và TongSLXuat trong bảng TONKHO của tất cả các vật tư theo Năm tháng truyền vào
ALTER PROC sp_TONKHO_CAPNHATTONGSL_NX
@NAMTHANG VARCHAR(6),
@MA_VT CHAR(4)
AS
BEGIN
	DECLARE @SL_NHAP INT
	DECLARE @SL_XUAT INT
	SET @SL_NHAP =(SELECT DBO.fn_TONGNHAPTHANG(@NAMTHANG,@MA_VT))
	SET @SL_XUAT = (SELECT DBO.fn_TONGXUATTHANG(@NAMTHANG,@MA_VT))
	IF EXISTS(SELECT * FROM TONKHO WHERE NAMTHANG=@NAMTHANG AND MAVTU=@MA_VT)
		UPDATE TONKHO SET TONGSLNHAP=@SL_NHAP, TONGSLXUAT=@SL_XUAT WHERE NAMTHANG=@NAMTHANG AND MAVTU=@MA_VT
	ELSE
	BEGIN
		DECLARE @SL INT
		SET @SL  = ISNULL((SELECT top 1 SLCUOI FROM TONKHO WHERE MAVTU=@MA_VT ORDER BY NAMTHANG DESC),0)
		INSERT INTO TONKHO(NAMTHANG,MAVTU,SLDAU,TONGSLNHAP,TONGSLXUAT) VALUES(@NAMTHANG,@MA_VT,@SL,@SL_NHAP,@SL_XUAT)
	END
END


SELECT * FROM TONKHO